package com.lsf.studentmanagerup.dao;

import com.lsf.studentmanagerup.bean.ClassBean;
import com.lsf.studentmanagerup.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * ClassBeanDao 实现类
 *
 * @author 李树飞
 */
public class ClassBeanDaoImp implements ClassBeanDao {

    /**
     * 根据班号查找班级
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public ClassBean getClassBeanById(String id) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //sql
        String sql = "select * from class where id = ?";

        //执行并返回结果
        return queryRunner.query(sql, new BeanHandler<>(ClassBean.class), id);
    }

    /**
     * 根据查询条件获取班级列表
     * @param condition
     * @return
     * @throws SQLException
     */
    @Override
    public List<ClassBean> getClassBeanByCondition(Map<String, String> condition) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

        //sql
        StringBuilder sql = new StringBuilder("select * from class where 1 = 1");
        List<Object> params = new ArrayList<>();
        //遍历condition，组成查询条件拼接到sql中
        for (Map.Entry<String, String> entry : condition.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            //如果zhi不为空则拼接起来
            if ((null != value && !("".equals(value)))) {
                sql.append(" and ").append(key).append(" like concat('%', ?, '%')");
                params.add(value);
            }
        }
        //执行并返回结果
        return queryRunner.query(sql.toString(), new BeanListHandler<>(ClassBean.class), params.toArray());
    }

    /**
     * 增加班级
     * @param classBean
     * @return
     * @throws SQLException
     */
    @Override
    public int addClassBean(ClassBean classBean) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "insert into class values(?, ?, ?, ?, ?, ?)";
        //参数
        Object[] params = {classBean.getId(), classBean.getName(), classBean.getGrade(),
                classBean.getTeacher(), classBean.getSlogen(), classBean.getNum()};
        //返回结果
        return queryRunner.update(sql, params);
    }

    /**
     * 删除班级
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int removeClassBeanById(int id) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "delete from class where id = ?";
        //返回结果
        return queryRunner.update(sql, id);
    }

    /**
     * 修改班级
     * @param classBean
     * @return
     * @throws SQLException
     */
    @Override
    public int updateClassBeanById(ClassBean classBean) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "update class set name = ?, grade = ?, teacher = ?, slogen = ? where id = ?";
        //参数
        Object[] params = {classBean.getName(), classBean.getGrade(),
                classBean.getTeacher(), classBean.getSlogen(), classBean.getId()};
        //返回结果
        return queryRunner.update(sql, params);
    }
}
